The approaches described in this section could be pursued, but are not necessarily approaches that have been previously conceived or pursued. Therefore, unless otherwise indicated herein, the approaches described in this section are not prior art to the claims in this application and are not admitted to be prior art by inclusion in this section.
Computers systems may be configured to store and retrieve large amounts of data. Typically, computer systems rely on database systems to perform this function. Data may be shared across multiple databases. Replication is the process of replicating data from a source database onto another database system, herein referred to as a target database. One approach to replication is the physical replication approach. Under the physical replication approach, the changes made to data blocks on the source database are made to replicas of those data blocks on a target database. Because the source database is replicated at the lowest atomic level of storage space on the target database, the target database is a physical replica of the source database. Another approach to replicating data is the logical replication approach. Under the logical replication approach, database commands that modify data on the source database are re-executed on the target database. While executing the same database commands guarantees that changes are replicated at the record level, the changes are not replicated at the data block level.
Under the logical replication approach, the target database does not need to have the same underlying database structure as the source database. For example, the target database may define different database structures than the source database, such as tables, views, and/or complex data types. Furthermore, not all changes made on the source database need to be made on the target database. For example, replication may be carried out for a subset of changes made on a source database.
To access or modify data stored in a database, applications interact with a database server by submitting database commands that cause the database server to perform operations on data stored in a database. A database command may conform to a syntax of a database language. One language for expressing database commands is Structured Query Language (“SQL”). SQL includes data manipulation language (“DML”) instructions and data definition language (“DDL”) instructions. DML instructions are used to manage data stored in database structures, such as tables, views, or complex data types, and other database objects. DDL instructions are used to define and modify database structures.
Mapping and filtering of database commands is useful when data is logically shared between databases that do not share the same underlying database structure and/or when only a subset of changes are to be replicated between a source database and a target database. The mapping and filtering of DML instructions is straightforward because DML instructions only act on one database object, such as a database table. However, this is not true for the mapping and filtering of DDL instructions. Complexities arise with mapping and/or filtering DDL instructions because of multiple reasons. For example, DDL instructions may involve multiple database objects, may affect multiple system tables, and may be dependent on the executing environment.
When processing replication data for DDL from a source database system, target database systems may attempt to compensate for these complexities by parsing the DDL text. For example, rudimentary mapping may be performed by replacing schema and object names present in the DDL text based on a text search and replace, but errors may arise when the same object or schema name is used for different database objects also present in the same DDL text. However, such parsing methods are error-prone because the target system may not know the exact grammar of the DDL being parsed and hence cannot be used for fine-grained mapping or filtering.
Based on the foregoing, it is desirable to develop an approach for data definition language (“DDL”) expression annotation.